from __future__ import annotations

import json
from typing import Any, Dict, Optional, Tuple, List

import frappe
from frappe.utils import now_datetime, now as now_str, flt


def _ensure_acct_balance(company: str, currency: str) -> str:
    """
    确保 Acct Balance 存在，返回其 name。
    逻辑参考 payment_manager._apply_balance_delta，但不变更余额，只做 upsert。
    """
    if not company or not currency:
        raise frappe.ValidationError("company 和 currency 不能为空")

    bal_name = frappe.db.get_value("Acct Balance", {"company": company, "currency": currency}, "name")
    if bal_name:
        return bal_name

    bal_doc = frappe.get_doc(
        {
            "doctype": "Acct Balance",
            "company": company,
            "currency": currency,
            "balance": 0,
            "reserved_logistics_today": 0,
            "last_recalc": now_datetime(),
        }
    )
    bal_doc.insert(ignore_permissions=True)
    return bal_doc.name


def _normalize_currency_code(code: str) -> str:
    """
    将外部返回的币种代码转换为 ERPNext 中存在的币种代码。
    - 云途会返回 RMB，而 ERPNext 默认使用 CNY 表示人民币。
    """
    if not code:
        return code
    code = code.strip().upper()
    if code == "RMB":
        return "CNY"
    return code


def _parse_yt_time_for_billing(value: Optional[str]) -> Optional[str]:
    """兼容云途时间格式，统一转为 MySQL 可接受的时间字符串，用于计费时间。

    云途返回可能是：
    - "2025-11-18T10:53:05+00:00"
    - "2025-11-18T10:53:05Z"
    - "2025-11-18 10:53:05"
    """
    if not value:
        return None
    try:
        cleaned = (
            value.replace("T", " ")
            .split(".")[0]
            .split("+")[0]
            .split("Z")[0]
            .strip()
        )
        return cleaned or None
    except Exception:
        return None


def _find_billing_cycle_for_bill_time(
    company: str,
    currency: str | None,
    bill_dt,
) -> Optional[str]:
    """
    根据计费时间匹配账期：
    - bill_dt 的日期 ∈ [period_start, period_end]
    - company 必须匹配
    - 若传入 currency，则同时按币种过滤
    - 优先使用未结算的账期（Status != Settled），按 period_start 正序取第一条
    """
    if not company or not bill_dt:
        return None

    from frappe.utils import getdate

    bill_date = getdate(bill_dt)

    filters: Dict[str, Any] = {
        "company": company,
        "period_start": ["<=", bill_date],
        "period_end": [">=", bill_date],
    }
    if currency:
        filters["currency"] = currency

    rows = frappe.get_all(
        "Account Billing Cycle Inst",
        filters=filters,
        fields=["name", "status", "period_start"],
        order_by="period_start asc",
        limit=10,
    )
    if not rows:
        return None

    # 先找非 Settled 的账期；若全是 Settled，则返回第一条
    for r in rows:
        if str(r.get("status")) != "Settled":
            return r.get("name")
    return rows[0].get("name")


def get_avg_unit_fee_for_route(
    *,
    company: str,
    product_code: str,
    destination_country: str,
) -> float:
    """
    按【公司 + 物流产品 + 目的国】统计昨天已计费的物流费，返回每 kg 平均单价。
    - 统计来源: Logistics Order  Inst
    - 使用 billed_amount / charge_weight 求平均值
    """
    from frappe.utils import add_days, nowdate

    try:
        today = nowdate()
        yesterday = add_days(today, -1)

        rows = frappe.get_all(
            "Logistics Order  Inst",
            filters={
                "company": company,
                "yt_product_code": product_code,
                "destination_country": destination_country,
                "billed_flag": 1,
                "billed_amount": [">", 0],
                "billed_at": ["between", [yesterday + " 00:00:00", yesterday + " 23:59:59"]],
            },
            fields=["billed_amount", "charge_weight", "actual_weight"],
        )

        if not rows:
            return 0.0

        total_amount = 0.0
        total_weight = 0.0
        for r in rows:
            try:
                amt = float(r.get("billed_amount") or 0)
                wt = float(r.get("charge_weight") or 0) or float(r.get("actual_weight") or 0)
            except Exception:
                continue
            if amt <= 0 or wt <= 0:
                continue
            total_amount += amt
            total_weight += wt

        if total_amount <= 0 or total_weight <= 0:
            return 0.0

        return total_amount / total_weight
    except Exception:
        return 0.0


def estimate_weight_from_sales_order(sales_order_name: str) -> float:
    """
    根据销售订单明细，用 qty * item.weight_per_unit 估算总重量（kg）。
    """
    try:
        so = frappe.get_doc("Sales Order", sales_order_name)
        if not so.items:
            return 0.0

        total_weight = 0.0
        for it in so.items:
            if not it.item_code or not it.qty:
                continue
            try:
                item_doc = frappe.get_doc("Item", it.item_code)
                unit_wt = float(getattr(item_doc, "weight_per_unit", 0) or 0)
                if unit_wt <= 0:
                    continue
                total_weight += float(it.qty) * unit_wt
            except Exception:
                continue

        return total_weight
    except Exception:
        return 0.0


def estimate_logistics_fee_for_order(
    sales_order_name: str,
    product_code: str,
    destination_country: str,
) -> Tuple[float, float, float]:
    """
    计算销售订单的预估物流费：
    - 先按 route 维度获取 avg_unit_fee（昨天平均每 kg）
    - 再用估算重量算出 estimated_fee
    - 返回 (estimated_fee, avg_unit_fee, estimated_weight)
    """
    so = frappe.get_doc("Sales Order", sales_order_name)
    company = so.company

    avg_unit_fee = get_avg_unit_fee_for_route(
        company=company,
        product_code=product_code,
        destination_country=destination_country,
    )

    estimated_weight = estimate_weight_from_sales_order(sales_order_name)
    if avg_unit_fee <= 0 or estimated_weight <= 0:
        return 0.0, avg_unit_fee, estimated_weight

    estimated_fee = avg_unit_fee * estimated_weight
    return estimated_fee, avg_unit_fee, estimated_weight


def _get_default_account_item_for_category(category: str) -> Optional[str]:
    """获取某个账目类别下标记为默认的 Account Item。"""
    if not category:
        return None
    try:
        rows = frappe.get_all(
            "Account Item",
            filters={"category": category, "is_default_for_category": 1, "is_active": 1},
            fields=["name"],
            limit=1,
        )
        if not rows:
            return None
        return rows[0].get("name")
    except Exception:
        return None


def _find_account_item_for_yt_fee(fee_name: str) -> Optional[str]:
    """
    根据云途返回的 fee_name 匹配内部 Account Item：
    - 使用 Account Item.yt_fee_name 精确匹配
    - 仅返回第一条匹配记录；未找到则返回 None
    """
    if not fee_name:
        return None
    try:
        name = frappe.db.get_value(
            "Account Item",
            {"yt_fee_name": fee_name},
            "name",
        )
        return name
    except Exception:
        return None


def _get_flowa_logis_fee_for_acct_item(company: str, acct_item: Optional[str], bill_dt) -> Tuple[float, bool]:
    """
    获取某个 Account Item 在指定公司的计费乘法系数：
    - 默认系数为 1.5（未配置 Pricing Plan 时）
    - 若 Pricing Plan.is_ignore_fee_item = 1，则返回 (0, True) 表示忽略该费用行
    - 若 Pricing Plan.fixed_price 已配置，则作为系数使用，且必须 > 1，否则抛异常
    """
    DEFAULT_MULTIPLIER = 1.5
    if not company or not acct_item or not bill_dt:
        return DEFAULT_MULTIPLIER, False

    try:
        from frappe.utils import getdate

        bill_date = getdate(bill_dt)

        rows = frappe.get_all(
            "Pricing Plan",
            filters={
                "company": company,
                "account_item": acct_item,
                # 只要求生效开始时间不晚于计费日期；若需要结束时间控制，可在界面上配置 effective_to
                "effective_from": ["<=", bill_date],
            },
            fields=["name", "fixed_price", "is_ignore_fee_item"],
            order_by="effective_from desc, creation desc",
            limit=1,
        )
        if not rows:
            frappe.logger().error(
                f"[Logistics Billing] no Pricing Plan matched, use default multiplier; company={company}, acct_item={acct_item}, bill_date={bill_date}"
            )
            return DEFAULT_MULTIPLIER, False

        row = rows[0]
        if row.get("is_ignore_fee_item"):
            frappe.logger().error(
                f"[Logistics Billing] fee item ignored by Pricing Plan; plan={row.get('name')}, acct_item={acct_item}, company={company}"
            )
            return 0.0, True

        multiplier = float(row.get("fixed_price") or DEFAULT_MULTIPLIER)
        # 若显式配置了系数，则必须大于 1
        if row.get("fixed_price") is not None and multiplier <= 1.0:
            frappe.throw(
                frappe._(
                    f"Pricing multiplier (fixed_price) for Account Item {acct_item} in company {company} must be greater than 1."
                )
            )
        frappe.logger().error(
            f"[Logistics Billing] use Pricing Plan multiplier; plan={row.get('name')}, acct_item={acct_item}, company={company}, bill_date={bill_date}, multiplier={multiplier}"
        )
        return multiplier, False
    except Exception:
        # 出现异常时回退默认系数，但不阻断主流程
        frappe.logger().error(
            f"[Logistics Billing] get multiplier failed, use default; company={company}, acct_item={acct_item}, bill_dt={bill_dt}, error={frappe.get_traceback()}"
        )
        return DEFAULT_MULTIPLIER, False


def _upsert_logistics_billing_details_for_los(
    *,
    los_doc,
    company: str,
    fee_details: List[Dict[str, Any]],
    transaction_dt,
    yt_currency: Optional[str],
    auto_approve: bool = False,
    batch_id: Optional[str] = None,
) -> int:
    """
    将云途费用明细同步到 Logistics Billing Detail Inst：
    - 不删除历史记录，保留完整的多次计费/拦截调整明细
    - 按 fee_details 逐条追加；若已存在“相同明细”则跳过，防止重复入库
    - “相同明细”的判定条件（以计费后的金额为准）：
      los + transaction_date + fee_name + acct_item + billed_amount 完全一致
    - 按 fee_name 去 Account Item.yt_fee_name 做映射，写入 acct_item 字段
    """
    if not los_doc or not getattr(los_doc, "name", None):
        return 0

    los_name = los_doc.name
    so_name = getattr(los_doc, "sales_order", None)
    waybill = getattr(los_doc, "waybill_number", None) or getattr(los_doc, "tracking_number", None)

    if not fee_details:
        return 0

    # 事务时间统一使用计费时间（billed_at）或云途 transaction_date
    tx_dt = transaction_dt
    inserted_count = 0

    for idx, fee in enumerate(fee_details, start=1):
        if not isinstance(fee, dict):
            continue
        try:
            fee_name = (fee.get("fee_name") or "").strip()
            try:
                amount = float(fee.get("amount") or 0)
            except Exception:
                amount = 0.0
            try:
                deduction_amount = float(fee.get("deduction_amount") or 0)
            except Exception:
                deduction_amount = 0.0

            line_currency = fee.get("currency") or yt_currency

            acct_item = _find_account_item_for_yt_fee(fee_name)
            # 若云途 fee name 无法映射到账目，使用 Logistic 类别下的默认账目
            if not acct_item:
                acct_item = _get_default_account_item_for_category("Logistics")
                if not acct_item:
                    # 未配置默认物流账目，则记录 warning 并跳过该行
                    frappe.logger().warning(
                        f"[Logistics Billing] no default Logistics Account Item configured; skip fee line fee_name={fee_name} los={los_name}"
                    )
                    continue

            # 计算该账目在 Flowa 体系下的物流计费系数（按交易日期选取合适的 Pricing Plan）；若配置为忽略则直接跳过
            multiplier, ignore_flag = _get_flowa_logis_fee_for_acct_item(company, acct_item, tx_dt)
            if ignore_flag:
                continue

            # 客户计费金额：按云途金额乘以计费系数（可为正/负，用于多退少补）
            billed_amount = flt(amount * multiplier, 4)

            # 防重复：若已有完全相同的一条明细（按计费后的金额判断），则跳过
            exists = frappe.db.exists(
                "Logistics Billing Detail Inst",
                {
                    "los": los_name,
                    "transaction_date": tx_dt,
                    "fee_name": fee_name,
                    "acct_item": acct_item,
                    "billed_amount": billed_amount,
                },
            )
            if exists:
                frappe.logger().info(
                    f"[Logistics Billing] skip duplicate fee line for los={los_name}, tx_dt={tx_dt}, fee_name={fee_name}, acct_item={acct_item}, billed_amount={billed_amount}"
                )
                continue

            # 审核信息：首单 PICKUP 场景自动审核，其它场景待人工审核
            approved_flag = 1 if auto_approve else 0
            approved_by = None
            approved_at = None
            if auto_approve:
                user = "Administrator"
                approved_by = user
                approved_at = tx_dt or now_datetime()

            # 为了方便客户对账查询，明细表中的金额字段也使用“客户计费后的金额”
            # 云途原始金额仍可从 LOS 上保存的 fee_detail_b64 JSON 中还原
            doc = frappe.get_doc(
                {
                    "doctype": "Logistics Billing Detail Inst",
                    "los": los_name,
                    "sales_order": so_name,
                    "waybill_number": waybill,
                    "batch_id": batch_id,
                    "transaction_date": tx_dt,
                    "account_index": idx,
                    "acct_item": acct_item,
                    "fee_name": fee_name,
                    # amount / deduction_amount 采用计费后的金额，便于客户直接按明细核对
                    "amount": billed_amount,
                    "currency": line_currency,
                    "rate": fee.get("rate"),
                    "deduction_currency": fee.get("deduction_currency"),
                    "deduction_amount": billed_amount,
                    "billed_amount": billed_amount,
                    "approved_flag": approved_flag,
                    "approved_by": approved_by,
                    "approved_at": approved_at,
                }
            )
            doc.insert(ignore_permissions=True)
            inserted_count += 1
        except Exception:
            # 某一行出错时仅跳过该行，不影响其它费用行
            frappe.logger().warning(
                f"[Logistics Billing] skip fee line due to error: fee_name={fee.get('fee_name')} los={los_name} trace={frappe.get_traceback()}"
            )
            continue

    return inserted_count


def finalize_logistics_billing_for_los(los_name: str) -> Dict[str, Any]:
    """
    在物流轨迹达到计费节点后，为单条 Logistics Order Inst 执行正式计费逻辑：
    - 调用云途费用接口获取 carrier 费用
    - 根据 Pricing Plan.logistics_base 计算客户计费金额
    - 从 Acct Balance 扣减余额 + 释放 reserved_logistics_today
    - 更新 LOS 的 billed_amount / billed_flag / billed_at 等字段

    说明：
    - 若此前已计费且本次获取的费用与原 billed_amount 不同，则按差额执行“多退少补”，并写入备注。
    """
    from frappe.utils import now
    from . import api_traking as tracking_mod

    try:
        doc = frappe.get_doc("Logistics Order Inst", los_name)

        if not doc.sales_order:
            return {"success": False, "message": "Logistics Order Inst 缺少 sales_order 字段"}

        so = frappe.get_doc("Sales Order", doc.sales_order)
        company = doc.company or so.company

        # 先调用接口获取 carrier 费用并回写基础字段
        resp = tracking_mod.yt_update_fee_for_los(los_name)
        if not isinstance(resp, dict) or not resp.get("success"):
            return {"success": False, "message": f"云途费用接口失败: {resp.get('message') if isinstance(resp, dict) else 'unknown'}"}

        data = resp.get("data") or {}
        carrier_fee = float(data.get("total_amount") or 0)
        # 费用为 0 也要走账期、预锁释放等逻辑（可能是完全优惠的订单）

        # 费用币种：优先使用云途返回的币种；若没有则回退到销售订单币种
        raw_currency = (data.get("currency") or "").strip() or so.currency
        currency = _normalize_currency_code(raw_currency)

        # 计费时间与所属账期：
        # - 优先使用云途返回的 transaction_date 作为计费日期，确保与承运商账单日期一致
        # - 若云途未返回时间，则复用 Logistics Order Inst 现有 billed_at
        # - 若仍然为空（首次且云途无时间），才回退为当前时间，但后续再次计费也会复用该 billed_at，避免每次跑批时间不同导致重复明细
        bill_dt_raw = data.get("transaction_date") or data.get("transactionTime")
        billed_at = _parse_yt_time_for_billing(bill_dt_raw)

        # 当前单据上已存在的计费时间（若有），用于在云途未返回时间时做兜底
        existing_billed_at = getattr(doc, "billed_at", None)
        existing_billed_at_norm = (
            _parse_yt_time_for_billing(str(existing_billed_at)) if existing_billed_at else None
        )

        # 保护逻辑：根据 Logistics Billing Detail Inst 中的最大 transaction_date 做幂等控制。
        # - 读取当前 LOS 在明细表中的最大 transaction_date；
        # - 若该时间与本次云途返回的 transaction_date 一致，则说明这一批费用已经完整按 Pricing Plan 处理过，直接跳过本次计费。
        if billed_at:
            try:
                latest_tx = frappe.db.get_value(
                    "Logistics Billing Detail Inst",
                    {"los": doc.name},
                    "max(transaction_date)",
                )
            except Exception:
                latest_tx = None

            latest_tx_norm = (
                _parse_yt_time_for_billing(str(latest_tx)) if latest_tx else None
            )
            if latest_tx_norm and billed_at == latest_tx_norm:
                return {
                    "success": True,
                    "carrier_fee": carrier_fee,
                    "customer_fee": float(getattr(doc, "billed_amount", 0) or 0),
                    "markup_ratio": 0.0,
                    "message": "Logistics billing already processed for this transaction_date; skip.",
                }

        # 若云途未返回时间，则复用已有的 billed_at；仅在完全没有值时才使用当前时间
        if not billed_at:
            if existing_billed_at:
                billed_at = existing_billed_at
            else:
                billed_at = now()
        billing_cycle_name = _find_billing_cycle_for_bill_time(company, currency, billed_at)

        # 本次计费批次号（用于追踪本次扣费涉及的所有明细与余额变更）
        batch_id = f"AUTO-{los_name}-{now_str()}"

        # 同步维护 YunExpress 费用明细到 Logistics Billing Detail Inst，并据此汇总客户计费金额
        inserted_count = 0
        try:
            fee_details: List[Dict[str, Any]] = data.get("fee_details") or []
            # PICKUP 首次计费场景：费用明细默认视为已审核（approved_flag=1，审核人为当前用户/管理员）
            inserted_count = _upsert_logistics_billing_details_for_los(
                los_doc=doc,
                company=company,
                fee_details=fee_details,
                transaction_dt=billed_at,
                yt_currency=data.get("currency"),
                auto_approve=True,
                batch_id=batch_id,
            )
        except Exception:
            frappe.logger().warning(
                f"[Logistics Billing] failed to upsert Logistics Billing Detail Inst for los={doc.name}: {frappe.get_traceback()}"
            )

        # 如果本次费用明细没有任何新增记录，则直接返回：
        # - 不更新 LOS 的 billed 字段
        # - 不更新 Acct Balance / 不写 Acct Balance Ledger
        # 适用于：云途当前还没有产生任何费用明细，或本次调用与上次完全相同
        if inserted_count == 0:
            return {
                "success": True,
                "carrier_fee": carrier_fee,
                "customer_fee": float(getattr(doc, "billed_amount", 0) or 0),
                "markup_ratio": 0.0,
                "message": "No new logistics billing details; skip LOS/balance/ledger update.",
            }

        # 根据已审核的明细汇总客户计费金额（billed_amount 的总和）；若没有明细则退回使用 carrier_fee
        try:
            detail_rows = frappe.get_all(
                "Logistics Billing Detail Inst",
                filters={"los": doc.name, "approved_flag": 1},
                fields=["billed_amount"],
                limit_page_length=0,
            )
            customer_fee = 0.0
            for r in detail_rows or []:
                try:
                    customer_fee += float(r.get("billed_amount") or 0)
                except Exception:
                    continue
            if not detail_rows:
                customer_fee = carrier_fee
        except Exception:
            # 汇总失败时退回使用 carrier_fee
            customer_fee = carrier_fee

        # 计算等效利润系数（仅用于返回给上层参考）
        markup_ratio = 0.0
        if carrier_fee not in (0, None):
            try:
                markup_ratio = customer_fee / carrier_fee
            except Exception:
                markup_ratio = 0.0

        # 余额与预锁字段（基于客户计费金额 customer_fee 进行多退少补）
        bal_name = _ensure_acct_balance(company, currency)
        bal_doc = frappe.get_doc("Acct Balance", bal_name)
        current_balance = float(bal_doc.balance or 0)
        current_reserved = float(getattr(bal_doc, "reserved_logistics_today", 0) or 0)
        reserved_fee = float(getattr(doc, "reserved_fee", 0) or 0)

        # 历史已计费用（基于利润后的金额）
        previous_billed = float(getattr(doc, "billed_amount", 0) or 0)

        # 本次实际应扣款金额：始终按 “本次汇总金额 - 历史已计费金额” 计算差额（支持多次追加明细，多退少补）
        charge_amount = customer_fee - previous_billed  # 正数多收，负数退款

        new_balance = current_balance - charge_amount

        # 预锁金额仅在首次计费时释放，之后调整不再动 reserved_today
        if previous_billed == 0:
            new_reserved_today = current_reserved - reserved_fee
        else:
            new_reserved_today = current_reserved
        if new_reserved_today < 0:
            new_reserved_today = 0.0

        # 在一个事务中更新所有数据
        # 更新 LOS：
        # - billed_amount / billed_flag 每次计费都更新为最新值（支持多退少补）
        # - billing_cycle / billed_at / billing_currency 只在首次计费时写入一次
        update_map_los = {
            "billed_amount": customer_fee,
            "billed_flag": 1,
        }
        # 首次计费时写入计费时间
        if not getattr(doc, "billed_at", None):
            update_map_los["billed_at"] = billed_at
        # 首次计费时写入账期
        if billing_cycle_name and not getattr(doc, "billing_cycle", None):
            update_map_los["billing_cycle"] = billing_cycle_name
        # 首次计费时写入计费币种（若前面尚未写入）
        if not getattr(doc, "billing_currency", None) and currency:
            update_map_los["billing_currency"] = currency

        frappe.db.set_value(
            "Logistics Order Inst",
            doc.name,
            update_map_los,
            update_modified=False,
        )
        # 清空本单 reserved_fee
        frappe.db.set_value(
            "Logistics Order Inst",
            doc.name,
            {"reserved_fee": 0},
            update_modified=False,
        )

        # 更新余额表
        frappe.db.set_value(
            "Acct Balance",
            bal_name,
            {
                "balance": new_balance,
                "reserved_logistics_today": new_reserved_today,
                "last_recalc": now_datetime(),
            },
            update_modified=False,
        )

        # 记录余额变更流水（自动计费批次）
        try:
            delta_balance = flt(new_balance - current_balance, 4)
            # 若本次运行对余额没有任何变化，则不记录流水，避免重复 0 记录
            if delta_balance:
                ledger_remark = f"Logistics auto billing for LOS {doc.name}, charge_amount={charge_amount:.2f}"
                ledger_doc = frappe.get_doc(
                    {
                        "doctype": "Acct Balance Ledger",
                        "acct_balance": bal_name,
                        "company": company,
                        "currency": currency,
                        "source_doctype": "Logistics Billing Detail Inst",
                        "batch_id": batch_id,
                        "delta": delta_balance,
                        "old_balance": current_balance,
                        "new_balance": new_balance,
                        "remark": ledger_remark,
                    }
                )
                ledger_doc.insert(ignore_permissions=True)
        except Exception:
            frappe.logger().warning(
                f"[Logistics Billing] failed to write Acct Balance Ledger for los={doc.name}: {frappe.get_traceback()}"
            )

        # 将本次已计费的明细行标记为 billed（仅针对已审核且尚未 billed 的行）
        try:
            frappe.db.sql(
                """
                update `tabLogistics Billing Detail Inst`
                set billed_flag = 1,
                    billed_at = %(billed_at)s,
                    billing_cycle = %(cycle)s
                where los = %(los)s
                  and ifnull(approved_flag, 0) = 1
                  and ifnull(billed_flag, 0) = 0
                """,
                {
                    "billed_at": billed_at,
                    "cycle": billing_cycle_name,
                    "los": doc.name,
                },
            )
        except Exception:
            frappe.logger().warning(
                f"[Logistics Billing] failed to mark detail rows billed for los={doc.name}: {frappe.get_traceback()}"
            )

        frappe.db.commit()

        return {
            "success": True,
            "carrier_fee": carrier_fee,
            "customer_fee": customer_fee,
            "markup_ratio": markup_ratio,
        }
    except Exception as e:
        frappe.logger().error(f"[Logistics Billing] 正式计费失败: {str(e)}")
        import traceback

        frappe.logger().error(f"[Logistics Billing] 异常堆栈: {traceback.format_exc()}")
        return {"success": False, "message": f"正式计费失败: {str(e)}"}

@frappe.whitelist()
def reserve_logistics_fee_for_order(sales_order_name: str) -> Dict[str, Any]:
    """
    预估并预锁定销售订单的物流费：
    - 使用历史均价 + 估算重量计算 estimated_fee
    - 将 estimated_fee 记录到 Logistics Order Inst.reserved_fee
    - 将 delta(新预估 - 旧预估) 累加到 Acct Balance.reserved_logistics_today
    - 返回 low_balance 标记，供前端提醒充值
    """
    try:
        so = frappe.get_doc("Sales Order", sales_order_name)
        company = so.company
        currency = so.currency

        # 物流产品代码 & 目的国
        product_code = (getattr(so, "custom_yt_product_code", None) or "").strip()
        # 目的国：优先使用 Sales Order 上的自定义字段 custom_country
        destination_country = (
            getattr(so, "custom_country", None)
            or getattr(so, "shipping_address_country", None)
            or ""
        )
        if not product_code or not destination_country:
            return {
                "success": False,
                "message": "缺少物流产品代码或目的国，无法预估物流费",
                "low_balance": False,
            }

        # 估算预扣费用
        estimated_fee, avg_unit_fee, estimated_weight = estimate_logistics_fee_for_order(
            sales_order_name, product_code, destination_country
        )

        # 获取或创建余额记录
        bal_name = _ensure_acct_balance(company, currency)
        bal_doc = frappe.get_doc("Acct Balance", bal_name)
        current_balance = float(bal_doc.balance or 0)
        current_reserved = float(getattr(bal_doc, "reserved_logistics_today", 0) or 0)

        # 找到对应的 Logistics Order Inst（最新一条）
        los_rows = frappe.get_all(
            "Logistics Order  Inst",
            filters={"sales_order": sales_order_name},
            fields=["name", "reserved_fee"],
            order_by="creation desc",
            limit=1,
        )

        los_name = los_rows[0].get("name") if los_rows else None
        old_reserved_fee = float(los_rows[0].get("reserved_fee") or 0) if los_rows else 0.0

        # 计算 delta，并更新 reserved_fee + reserved_logistics_today
        delta = estimated_fee - old_reserved_fee
        new_reserved_today = current_reserved + delta
        if new_reserved_today < 0:
            new_reserved_today = 0.0

        # 更新余额的 reserved_logistics_today
        frappe.db.set_value(
            "Acct Balance",
            bal_name,
            {
                "reserved_logistics_today": new_reserved_today,
                "last_recalc": now_datetime(),
            },
            update_modified=False,
        )

        # 更新 LOS 预估金额
        if los_name:
            frappe.db.set_value(
                "Logistics Order  Inst",
                los_name,
                {"reserved_fee": estimated_fee},
                update_modified=False,
            )

        available_after_reserve = current_balance - new_reserved_today
        low_balance = available_after_reserve <= 0

        return {
            "success": True,
            "estimated_fee": estimated_fee,
            "avg_unit_fee": avg_unit_fee,
            "estimated_weight": estimated_weight,
            "balance": current_balance,
            "reserved_today": new_reserved_today,
            "available_after_reserve": available_after_reserve,
            "low_balance": low_balance,
        }
    except Exception as e:
        frappe.logger().error(f"[Logistics Reserve] 预扣费失败: {str(e)}")
        import traceback

        frappe.logger().error(f"[Logistics Reserve] 异常堆栈: {traceback.format_exc()}")
        return {"success": False, "message": f"预扣费失败: {str(e)}", "low_balance": False}


@frappe.whitelist()
def approve_logistics_billing_details(detail_names, charge_reason: str | None = None) -> Dict[str, Any]:
    """
    人工审核并扣费：
    - detail_names: 前端勾选的 Logistics Billing Detail Inst.name 列表（或 JSON 字符串）
    - 仅处理 billed_flag=0 的行，避免重复扣费
    - 审核通过的明细：approved_flag=1, approved_by=当前用户, approved_at=now, charge_reason
    - 根据这些明细的 billed_amount 汇总，按 LOS / 公司 / 币种扣减 Acct Balance，并更新 LOS.billed_amount
    - 为每条明细写入 billed_flag/billed_at/billing_cycle
    """
    try:
        from frappe.utils import now_datetime

        if isinstance(detail_names, str):
            try:
                detail_names = json.loads(detail_names)
            except Exception:
                detail_names = [d for d in detail_names.split(",") if d]

        if not detail_names:
            return {"success": False, "message": "No details selected"}

        # 只取未 billed 的明细，避免重复扣费
        details = frappe.get_all(
            "Logistics Billing Detail Inst",
            filters={"name": ["in", detail_names], "billed_flag": ["in", [0, None]]},
            fields=[
                "name",
                "los",
                "transaction_date",
                "currency",
                "billed_amount",
                "billing_cycle",
            ],
            limit_page_length=0,
        )
        if not details:
            return {"success": False, "message": "No pending details to approve"}

        now_ts = now_datetime()
        batch_id = f"MANUAL-{now_str()}"
        user = getattr(frappe.session, "user", None) or "Administrator"

        # 按 LOS 分组，便于更新主单和余额
        by_los: Dict[str, List[Dict[str, Any]]] = {}
        for d in details:
            los_name = d.get("los")
            if not los_name:
                continue
            by_los.setdefault(los_name, []).append(d)

        total_delta = 0.0
        affected_los: list[str] = []

        for los_name, items in by_los.items():
            if not items:
                continue
            try:
                los_doc = frappe.get_doc("Logistics Order Inst", los_name)
            except Exception:
                continue

            # 公司与计费币种
            so = frappe.get_doc("Sales Order", los_doc.sales_order) if los_doc.sales_order else None
            company = los_doc.company or (so.company if so else None)
            bal_currency = (
                getattr(los_doc, "billing_currency", None)
                or (getattr(so, "currency", None) if so else None)
                or items[0].get("currency")
            )
            currency = _normalize_currency_code(bal_currency or "")
            if not company or not currency:
                continue

            # 本次这张 LOS 要扣的总额（仅选中的这些行）
            delta_los = 0.0
            for it in items:
                try:
                    delta_los += float(it.get("billed_amount") or 0)
                except Exception:
                    continue
            if abs(delta_los) <= 0.0001:
                continue

            # 更新余额表，并记录余额变更流水（人工审核批次）
            bal_name = _ensure_acct_balance(company, currency)
            current_balance = float(frappe.db.get_value("Acct Balance", bal_name, "balance") or 0)
            new_balance = current_balance - delta_los
            frappe.db.set_value(
                "Acct Balance",
                bal_name,
                {"balance": new_balance, "last_recalc": now_datetime()},
                update_modified=False,
            )

            try:
                delta_balance = flt(new_balance - current_balance, 4)
                # 若人工审核本次对余额没有实质变化，则不写入流水
                if delta_balance:
                    ledger_remark = (
                        f"Manual approve logistics fees for LOS {los_name}, rows={len(items)}, delta={delta_los:.2f}"
                    )
                    ledger_doc = frappe.get_doc(
                        {
                            "doctype": "Acct Balance Ledger",
                            "acct_balance": bal_name,
                            "company": company,
                            "currency": currency,
                            "source_doctype": "Logistics Billing Detail Inst",
                            "batch_id": batch_id,
                            "delta": delta_balance,
                            "old_balance": current_balance,
                            "new_balance": new_balance,
                            "remark": ledger_remark,
                        }
                    )
                    ledger_doc.insert(ignore_permissions=True)
            except Exception:
                frappe.logger().warning(
                    f"[Logistics Billing] failed to write Acct Balance Ledger for manual approve los={los_name}: {frappe.get_traceback()}"
                )

            # 更新 LOS.billed_amount（累加本次 delta）
            previous_billed = float(getattr(los_doc, "billed_amount", 0) or 0)
            new_billed_total = previous_billed + delta_los
            frappe.db.set_value(
                "Logistics Order Inst",
                los_name,
                {"billed_amount": new_billed_total, "billed_flag": 1},
                update_modified=False,
            )

            # 更新每条明细的审核 / 计费状态
            for it in items:
                bill_dt = it.get("transaction_date") or now_ts
                cycle = _find_billing_cycle_for_bill_time(company, currency, bill_dt)
                upd: Dict[str, Any] = {
                    "approved_flag": 1,
                    "approved_by": user,
                    "approved_at": now_ts,
                    "billed_flag": 1,
                    "billed_at": now_ts,
                    "charge_reason": charge_reason,
                }
                if cycle and not it.get("billing_cycle"):
                    upd["billing_cycle"] = cycle
                frappe.db.set_value(
                    "Logistics Billing Detail Inst", it.get("name"), upd, update_modified=False
                )

            total_delta += delta_los
            affected_los.append(los_name)

        frappe.db.commit()

        return {
            "success": True,
            "message": f"Approved {len(details)} detail rows, total charged {total_delta:.2f}",
            "affected_los": affected_los,
            "total_charged": total_delta,
        }
    except Exception as e:
        frappe.logger().error(f"[Logistics Billing] approve_logistics_billing_details failed: {str(e)}")
        import traceback

        frappe.logger().error(f"[Logistics Billing] approve exception: {traceback.format_exc()}")
        return {"success": False, "message": str(e)}

